package com.shyroke.daydayzhuandao.respository;

import com.shyroke.daydayzhuanapi.dto.EffectProjectDto;
import com.shyroke.daydayzhuanapi.dto.ProjectCommentDto;
import com.shyroke.daydayzhuanapi.dto.ProjectDto;
import com.shyroke.daydayzhuanapi.dto.TopProjectDto;
import com.shyroke.daydayzhuanapi.entity.ProjectEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

@Repository
public interface ProjectRepository extends BaseRepository<ProjectEntity,Integer>{
    @Query(value = "select new com.shyroke.daydayzhuanapi.dto.ProjectCommentDto(p.id,p.createTime,p.title,p.cover, p.showLabel)  from ProjectEntity p where p.commented = ?1 and p.offline = ?2 order by p.sortIndex desc,p.createTime desc")
    List<ProjectCommentDto> findByCommentedAndOfflineOrderByCreateTimeDesc(String commented, String offline);

    List<ProjectEntity> findByIsNofollowOrderByCreateTimeDesc(int isNofollow);

    @Query(nativeQuery = true,value = "   select  p.type,p.id,p.title,t.id from project p " +
            "    left join type t " +
            "    on t.id = p.type_id " +
            "    where p.id in (  " +
            "        select id from project where type_id = ?2 ORDER BY create_time desc  " +
            "    ) and p.id != ?1 and offline = '0'  ORDER BY create_time desc limit 0,10 ")
    List<ProjectEntity> getLikeLabelProjectList(Integer projectId,Integer typeId);

    @Query(nativeQuery = true,value = "    (select id,title from project where id < ?1  order by id desc limit 1) " +
            "    UNION " +
            "    (select id,title from project where id > ?1  order by id asc limit 1) ")
    List<Map<String,Object>> getNextAndPre(Integer projectId);

    @Query(nativeQuery = true,value = "select count(0) as amount,sum(read_mount) as readSum from project where create_id = ?1")
    Map<String, Object> totalCountByUserId(Integer uid);

    @Query(nativeQuery = true,value = " select * from project where create_time >= ?1 and create_time  <= ?2 ")
    List<ProjectEntity> findByWeek(String beginDate, String endDate);

    @Query(nativeQuery = true,value = " select sum(comment_mount) from project ")
    Integer commentCount();

    @Query(nativeQuery = true,value = "  select count(0) from project where create_id = ?1 ")
    int countByUid(Integer uid);

    @Query(nativeQuery = true,value = " select * from project where create_time >= ?1 and create_time  <= ?2 and create_id = ?3 ")
    List<ProjectEntity> findByWeekAndUid(String beginDate, String endDate, Integer uid);

    @Query(nativeQuery = true,value = " select sum(comment_mount) from project where  create_id = ?1 ")
    Integer commentCountByUid(Integer uid);

    @Query(nativeQuery = true,value = "  select * from project where create_id = ?1 order by create_time desc limit ?2 ")
    List<ProjectEntity> getNewProjectListFiveByUserId(Integer uid, int count);

    List<ProjectEntity> findByTypeIdOrderByCreateTimeDesc(Integer typeId);

    @Modifying
    @Transactional(rollbackFor = Exception.class)
    @Query("update ProjectEntity p set p.readMount = p.readMount+1 where p.id = ?1")
    void updateCount(Integer projectId);

    @Query(nativeQuery = true,value = "select sum(cast(profit as decimal(18,2))) from project where create_time >= ?1 and create_time <= ?2 and profit is not null")
    BigDecimal totalProfitToday(String begin, String end);

    @Query(value = "select new com.shyroke.daydayzhuanapi.dto.TopProjectDto(p.id, p.createTime, p.title, p.isNofollow, p.showLabel) from  ProjectEntity p where p.typeId = ?1 and p.commented = '1' order by p.id desc")
    List<TopProjectDto> findCommendByTypeId(Integer typeId);

    Page<ProjectEntity> findByProfitIsNull(Pageable pageable);

    @Query(nativeQuery = true, value = "select count(0) from project where type_id = 1")
    Integer findYangmaoArticleCount();

    @Query(nativeQuery = true, value = "select sum(profit) from project where profit is not null")
    BigDecimal findAllProfit();
}
